package service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import model.AttributeDefinition;
import model.Customer;
import utils.DBUtil;

/**
 * 客户服务类，提供客户相关的数据库操作
 */
public class CustomerService {
    
    /**
     * 添加客户信息
     * @param customer 客户对象
     * @return 新增客户的ID，失败返回-1
     */
    public int addCustomer(Customer customer) {
        Connection conn = null;
        PreparedStatement pstmtCustomer = null;
        PreparedStatement pstmtAttribute = null;
        ResultSet generatedKeys = null;
        int customerId = -1;
        
        try {
            conn = DBUtil.connect();
            conn.setAutoCommit(false);
            
            // 1. 添加客户基本信息
            String sqlCustomer = "INSERT INTO customers (customer_name) VALUES (?)";
            pstmtCustomer = conn.prepareStatement(sqlCustomer, Statement.RETURN_GENERATED_KEYS);
            pstmtCustomer.setString(1, customer.getCustomerName());
            
            int affectedRows = pstmtCustomer.executeUpdate();
            if (affectedRows == 0) {
                throw new SQLException("创建客户失败，未获取到ID");
            }
            
            generatedKeys = pstmtCustomer.getGeneratedKeys();
            if (generatedKeys.next()) {
                customerId = generatedKeys.getInt(1);
                customer.setCustomerId(customerId);
            } else {
                throw new SQLException("创建客户失败，未获取到ID");
            }
            
            // 2. 添加客户属性值
            if (!customer.getAttributeValues().isEmpty()) {
                String sqlAttribute = "INSERT INTO customer_attribute_values (customer_id, attribute_id, string_value, is_heightlight) VALUES (?, ?, ?, ?)";
                pstmtAttribute = conn.prepareStatement(sqlAttribute);
                
                for (Map.Entry<Integer, String> entry : customer.getAttributeValues().entrySet()) {
                    int attributeId = entry.getKey();
                    String value = entry.getValue();
                    
                    if (value != null && !value.isEmpty()) {
                        pstmtAttribute.setInt(1, customerId);
                        pstmtAttribute.setInt(2, attributeId);
                        pstmtAttribute.setString(3, value);
                        
                        // 设置高亮状态
                        boolean isHighlighted = customer.isAttributeHighlighted(attributeId);
                        pstmtAttribute.setString(4, isHighlighted ? "是" : "否");
                        
                        pstmtAttribute.addBatch();
                    }
                }
                
                pstmtAttribute.executeBatch();
            }
            
            conn.commit();
            return customerId;
            
        } catch (SQLException e) {
            if (conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            e.printStackTrace();
            return -1;
        } finally {
            try {
                if (generatedKeys != null) generatedKeys.close();
                if (pstmtAttribute != null) pstmtAttribute.close();
                if (pstmtCustomer != null) pstmtCustomer.close();
                if (conn != null) {
                    conn.setAutoCommit(true);
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    
    /**
     * 更新客户信息
     * @param customer 客户对象
     * @return 更新是否成功
     */
    public boolean updateCustomer(Customer customer) {
        Connection conn = null;
        PreparedStatement pstmtCustomer = null;
        PreparedStatement pstmtDeleteAttr = null;
        PreparedStatement pstmtInsertAttr = null;
        
        try {
            conn = DBUtil.connect();
            conn.setAutoCommit(false);
            
            // 1. 更新客户基本信息
            String sqlCustomer = "UPDATE customers SET customer_name = ? WHERE customer_id = ?";
            pstmtCustomer = conn.prepareStatement(sqlCustomer);
            pstmtCustomer.setString(1, customer.getCustomerName());
            pstmtCustomer.setInt(2, customer.getCustomerId());
            pstmtCustomer.executeUpdate();
            
            // 2. 删除原有属性值
            String sqlDeleteAttr = "DELETE FROM customer_attribute_values WHERE customer_id = ?";
            pstmtDeleteAttr = conn.prepareStatement(sqlDeleteAttr);
            pstmtDeleteAttr.setInt(1, customer.getCustomerId());
            pstmtDeleteAttr.executeUpdate();
            
            // 3. 添加新的属性值
            if (!customer.getAttributeValues().isEmpty()) {
                String sqlInsertAttr = "INSERT INTO customer_attribute_values (customer_id, attribute_id, string_value, is_heightlight) VALUES (?, ?, ?, ?)";
                pstmtInsertAttr = conn.prepareStatement(sqlInsertAttr);
                
                for (Map.Entry<Integer, String> entry : customer.getAttributeValues().entrySet()) {
                    int attributeId = entry.getKey();
                    String value = entry.getValue();
                    
                    if (value != null && !value.isEmpty()) {
                        pstmtInsertAttr.setInt(1, customer.getCustomerId());
                        pstmtInsertAttr.setInt(2, attributeId);
                        pstmtInsertAttr.setString(3, value);
                        
                        // 设置高亮状态
                        boolean isHighlighted = customer.isAttributeHighlighted(attributeId);
                        pstmtInsertAttr.setString(4, isHighlighted ? "是" : "否");
                        
                        pstmtInsertAttr.addBatch();
                    }
                }
                
                pstmtInsertAttr.executeBatch();
            }
            
            conn.commit();
            return true;
            
        } catch (SQLException e) {
            if (conn != null) {
                try {
                    conn.rollback();
                } catch (SQLException ex) {
                    ex.printStackTrace();
                }
            }
            e.printStackTrace();
            return false;
        } finally {
            try {
                if (pstmtInsertAttr != null) pstmtInsertAttr.close();
                if (pstmtDeleteAttr != null) pstmtDeleteAttr.close();
                if (pstmtCustomer != null) pstmtCustomer.close();
                if (conn != null) {
                    conn.setAutoCommit(true);
                    conn.close();
                }
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    }
    
    /**
     * 删除客户信息
     * @param customerId 客户ID
     * @return 删除是否成功
     */
    public boolean deleteCustomer(int customerId) {
        String sql = "DELETE FROM customers WHERE customer_id = ?";
        
        try (Connection conn = DBUtil.connect();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, customerId);
            
            int affectedRows = pstmt.executeUpdate();
            return affectedRows > 0;
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }
    }
    
    /**
     * 获取所有客户信息
     * @return 客户列表
     */
    public List<Customer> getAllCustomers() {
        List<Customer> customers = new ArrayList<>();
        
        try (Connection conn = DBUtil.connect();
             PreparedStatement stmt = conn.prepareStatement("SELECT customer_id, customer_name FROM customers");
             ResultSet rs = stmt.executeQuery()) {
            
            while (rs.next()) {
                Customer customer = new Customer();
                customer.setCustomerId(rs.getInt("customer_id"));
                customer.setCustomerName(rs.getString("customer_name"));
                
                // 加载客户的属性值
                loadCustomerAttributes(customer, conn);
                
                customers.add(customer);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        return customers;
    }
    
    /**
     * 根据ID获取客户信息
     * @param customerId 客户ID
     * @return 客户对象
     */
    public Customer getCustomerById(int customerId) {
        String sql = "SELECT customer_id, customer_name FROM customers WHERE customer_id = ?";
        
        try (Connection conn = DBUtil.connect();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            pstmt.setInt(1, customerId);
            
            try (ResultSet rs = pstmt.executeQuery()) {
                if (rs.next()) {
                    Customer customer = new Customer();
                    customer.setCustomerId(rs.getInt("customer_id"));
                    customer.setCustomerName(rs.getString("customer_name"));
                    
                    // 加载客户的属性值
                    loadCustomerAttributes(customer, conn);
                    
                    return customer;
                }
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        return null;
    }
    
    /**
     * 加载客户的属性值
     * @param customer 客户对象
     * @param conn 数据库连接
     */
    private void loadCustomerAttributes(Customer customer, Connection conn) throws SQLException {
        String sql = "SELECT attribute_id, string_value, is_heightlight FROM customer_attribute_values WHERE customer_id = ?";
        
        try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
            pstmt.setInt(1, customer.getCustomerId());
            
            try (ResultSet rs = pstmt.executeQuery()) {
                while (rs.next()) {
                    int attributeId = rs.getInt("attribute_id");
                    String value = rs.getString("string_value");
                    String isHeightlight = rs.getString("is_heightlight");
                    
                    // 设置属性值
                    customer.setAttributeValue(attributeId, value);
                    
                    // 设置高亮状态 - "是"对应true，"否"或null对应false
                    boolean isHighlighted = "是".equals(isHeightlight);
                    customer.setAttributeHighlight(attributeId, isHighlighted);
                }
            }
        }
    }
    
    /**
     * 搜索客户
     * @param keyword 搜索关键词
     * @return 客户列表
     */
    public List<Customer> searchCustomers(String keyword) {
        List<Customer> customers = new ArrayList<>();
        String sql = "SELECT DISTINCT c.customer_id, c.customer_name FROM customers c " +
                     "LEFT JOIN customer_attribute_values v ON c.customer_id = v.customer_id " +
                     "WHERE c.customer_name LIKE ? OR v.string_value LIKE ?";
        
        try (Connection conn = DBUtil.connect();
             PreparedStatement pstmt = conn.prepareStatement(sql)) {
            
            String searchPattern = "%" + keyword + "%";
            pstmt.setString(1, searchPattern);
            pstmt.setString(2, searchPattern);
            
            try (ResultSet rs = pstmt.executeQuery()) {
                Map<Integer, Customer> customerMap = new HashMap<>();
                
                while (rs.next()) {
                    int customerId = rs.getInt("customer_id");
                    
                    // 避免重复添加同一个客户
                    if (!customerMap.containsKey(customerId)) {
                        Customer customer = new Customer();
                        customer.setCustomerId(customerId);
                        customer.setCustomerName(rs.getString("customer_name"));
                        
                        // 加载客户的属性值
                        loadCustomerAttributes(customer, conn);
                        
                        customerMap.put(customerId, customer);
                    }
                }
                
                customers.addAll(customerMap.values());
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        
        return customers;
    }
} 